package com.creating.mict.dao;


import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.creating.mict.common.DateUtils;
import com.creating.mict.domain.Contact;
import com.creating.mict.domain.Group;
import com.creating.mict.domain.Patient;
import com.creating.mict.domain.PatientSetting;

public class PatientDao extends HibernateDaoSupport {

private static final Log log = LogFactory.getLog(PatientDao.class);
	
	
/**
 * 
* @Title: queryPatientSigned 
* @Description: 获取签约患者
* @param @param loginName
* @param @param fbtime
* @param @param fetime
* @param @param fkeywords
* @param @param foffice
* @param @return 
* @return List<Patient>    返回类型 
* @throws
 */
public List<Patient> queryPatientSigned(String loginName, String loginType, String fbtime, String fetime, String fstate, String fkeywords, String foffice) {
		String sql = "";
		sql = " select a.fpid, a.fbdate, a.fedate, fsign_doctor, a.fybkh, a.fidcd, a.fname, a.fsex, a.fphone1, a.fadd_detail, a.fxyjc, a.fxtjc, a.fxzjc, a.fxnsjc, a.fxjgjc, a.fxybhdjc, a.fydjc, a.fxdjc, a.fwzfw, a.fstate, a.foffice, a.fphone2,b.fname fgroup "
			+ " from tds_patient a, tds_group b " 
			+ " where a.fgroup = b.fid "; 
		
		if(fetime != null && !(fetime.equals("null")) && !(fetime.equals(""))) {
			sql += " and a.fedate <= to_date('" + fetime + " 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
		}
		
//		if((fbtime == null || fbtime.equals("null") || fbtime.equals("")) && fetime != null && !(fetime.equals("")) ) {
//			sql += " and a.fedate <= to_date('" + fetime + " 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
//		} else if(fetime == null || fetime.equals("null") || fetime.equals("")){
//			
//		} else {
//			sql += " and a.fedate between to_date('" + fbtime + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + fetime + " 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
//		}
		
		if(fkeywords == null || fkeywords.equals("null")) {
			//do nothing
		} else {
			sql	+= " and (a.fname like '" + fkeywords + "%' or a.fname_pym like '" + fkeywords + "%' "
				 + " or a.fphone1 like '" + fkeywords + "%') ";
		}
		
		if(fstate != null && fstate.equals("0")) {
			sql	+= " and a.fstate='1' ";
		} else {
			
		}
		
//		if(loginType.equals("3") || loginType.equals("1")) {
		if(loginType.equals("3")) {
			sql	+= " and a.FSIGN_DOCTOR='" + loginName + "' ";
		} else {
			
		}
		
		if(foffice == null || foffice.equals("null") || foffice.equals("") ) {
//			sql	+= " and a.foffice in ('-100') ";
		} else {
			sql	+= " and a.foffice in (" + foffice + ") ";
		}
		
		sql	+= " order by a.fname asc ";
		
		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		List<Patient> pa_list = new ArrayList<Patient>();
		
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			Patient pa = new Patient();
			for (int j = 0; j < obj.length; j++) {
				pa.setFpid("" + obj[0]);
				pa.setFbdateStr("" + obj[1]);
				pa.setFedateStr("" + obj[2]);
				pa.setFsign_doctor("" + obj[3]);
				pa.setFybkh("" + obj[4]);
				pa.setFidcd("" + obj[5]);
				pa.setFname("" + obj[6]);
				pa.setFsex("" + obj[7]);
				pa.setFphone1("" + obj[8]);
				pa.setFadd_detail("" + obj[9]);
				
				pa.setFxyjc("" + obj[10]);
				pa.setFxtjc("" + obj[11]);
				pa.setFxzjc("" + obj[12]);
				pa.setFxnsjc("" + obj[13]);
				pa.setFxjgjc("" + obj[14]);
				pa.setFxybhdjc("" + obj[15]);
				pa.setFydjc("" + obj[16]);
				pa.setFxdjc("" + obj[17]);
				pa.setFwzfw("" + obj[18]);
				pa.setFstate("" + obj[19]);
				pa.setFoffice("" + obj[20]);
				pa.setFphone2("" + obj[21]);
				pa.setFgroup("" + obj[22]);
			}
			pa_list.add(pa);
		}
		return pa_list;
	}

	/**
	 * 
	* @Title: queryPatientByKeyWords 
	* @Description: 根据身份证号、医保号、uid、手机号获取患者对象
	* @param @return 
	* @return Patient    返回类型 
	* @throws
	 */
	public Patient queryPatientByKeyWords(String fkeywords) {
		String sql = "";
		sql = " select a.fpid, a.fname, a.fphone1, a.FIDCD, a.fybkh, a.foffice "
			+ " from tds_patient a " 
			+ " where 1=1 "; 
		
		if(fkeywords == null || fkeywords.equals("null")) {
			//do nothing
		} else {
			sql	+= " and (a.fpid = '" + fkeywords + "' or a.FIDCD = '" + fkeywords + "' "
				 + " or a.fphone1 = '" + fkeywords + "' or a.FYBKH = '" + fkeywords + "' ) ";
		}
		
		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		if(list.size() <= 0) {
			return null;
		}
		Patient patient = new Patient();
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			for (int j = 0; j < obj.length; j++) {
				patient.setFpid("" + obj[0]);
				patient.setFidcd("" + obj[3]);
				patient.setFname("" + obj[1]);
				patient.setFphone1("" + obj[2]);
				patient.setFybkh("" + obj[4]);
				patient.setFoffice("" + obj[5]);
			}
		}
		return patient;
	}

	/**
	 * 
	* @Title: changeSignState 
	* @Description: 更新签约状态
	* @param @param fpid
	* @param @param fstate
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean changeSignState(String fpid, String fstate) {
		try {
			String sql = "update tds_patient set fstate='" + fstate + "' " 
					   + " where fpid in (" + fpid + ") ";
			
			Query query = this.getSession().createSQLQuery(sql);
			int num = query.executeUpdate();
			if (num > 0) {
				return true;
			} else {
				return true;
			}

		} catch (RuntimeException re) {
			log.error("更新签约状态失败！", re);
			throw re;
		}
	}
	
	/**
	 * 
	* @Title: groupingPatient 
	* @Description: 分组 
	* @param @param fid
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean groupingPatient(String fid, String fgroup) {
		try {
			String sql = "update tds_patient set fgroup='" + fgroup + "' " 
					   + " where fpid in (" + fid + ") ";
			
			Query query = this.getSession().createSQLQuery(sql);
			int num = query.executeUpdate();
			if (num > 0) {
				return true;
			} else {
				return true;
			}

		} catch (RuntimeException re) {
			log.error("分组失败！", re);
			throw re;
		}
	}
	
	/**
	 * 
	* @Title: deleteSignInfo 
	* @Description: 删除签约信息
	* @param @param fpid
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean deleteSignInfo(String fpid) {
		try {
			String sql = "delete tds_patient " 
					   + " where fpid in (" + fpid + ") ";
			
			Query query = this.getSession().createSQLQuery(sql);
			int num = query.executeUpdate();
			if (num > 0) {
				log.info("删除签约信息成功！");
				return true;
			} else {
				return true;
			}

		} catch (RuntimeException re) {
			log.error("删除签约信息失败！", re);
			throw re;
		}
	}
	
	/**
	 * 
	* @Title: getPatientInfoById 
	* @Description: 根据id获取签约患者
	* @param @return 
	* @return PatientInfo    返回类型 
	* @throws
	 */
	public Patient getPatientInfoById(String fpid) {
		String sql = "";
		sql = " select a.fpid, a.fbdate, a.fedate, fsign_doctor, a.fybkh, a.fidcd, a.fname, a.fsex, a.fphone1, a.fadd_detail, "
			+ " a.fxyjc, a.fxtjc, a.fxzjc, a.fxnsjc, a.fxjgjc, a.fxybhdjc, a.fydjc, a.fxdjc, a.fwzfw, "
			+ " a.fstate, a.FOFFICE, fname_pym, fupdate, fsign_date, a.fphone2, a.fgroup "
				+ " from tds_patient a " 
				+ " where a.fpid='" + fpid + "' ";

		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		if(list.size() <= 0) {
			return null;
		}
		
		this.getSession().close();
		
		Patient pa = new Patient();
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			for (int j = 0; j < obj.length; j++) {
				pa.setFpid("" + obj[0]);
				pa.setFbdateStr("" + obj[1]);
				pa.setFedateStr("" + obj[2]);
				pa.setFsign_doctor("" + obj[3]);
				pa.setFybkh("" + obj[4]);
				pa.setFidcd("" + obj[5]);
				pa.setFname("" + obj[6]);
				pa.setFsex("" + obj[7]);
				pa.setFphone1("" + obj[8]);
				pa.setFadd_detail("" + obj[9]);
				
				pa.setFxyjc("" + obj[10]);
				pa.setFxtjc("" + obj[11]);
				pa.setFxzjc("" + obj[12]);
				pa.setFxnsjc("" + obj[13]);
				pa.setFxjgjc("" + obj[14]);
				pa.setFxybhdjc("" + obj[15]);
				pa.setFydjc("" + obj[16]);
				pa.setFxdjc("" + obj[17]);
				pa.setFwzfw("" + obj[18]);
				pa.setFstate("" + obj[19]);
				pa.setFoffice("" + obj[20]);
				
				pa.setFname_pym("" + obj[21]);
				pa.setFupdate("" + obj[22]);
				pa.setFsign_date(DateUtils.parseDate("" + obj[23]));
				pa.setFphone2("" + obj[24]);
				pa.setFgroup("" + obj[25]);
			}
		}
		return pa;
	}
	
	/**
	 * 
	* @Title: queryTipYbkh 
	* @Description: 根据医保卡号模糊查询
	* @param @param fybkh
	* @param @return 
	* @return List<Patient>    返回类型 
	* @throws
	 */
	public List<Patient> queryTipYbkh(String fybkh) {
		String sql = "";
		sql = " select a.fk_citi_id, a.fname, a.fsex, a.fidcd, a.fltel, a.fjwh, a.fadd_detail, a.fchnum "
			+ " from tcitizen a " 
			+ " where a.fchnum like '%" + fybkh + "%' "; 
		
		sql	+= " order by a.fname asc ";
		
		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		List<Patient> pa_list = new ArrayList<Patient>();
		
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			Patient pa = new Patient();
			for (int j = 0; j < obj.length; j++) {
				pa.setFpid("" + obj[0]);
				pa.setFname("" + obj[1]);
				pa.setFsex("" + obj[2]);
				pa.setFidcd("" + obj[3]);
				pa.setFphone1("" + obj[4]);
				
				pa.setFadd_detail("" + obj[6]);
				pa.setFybkh("" + obj[7]);
			}
			pa_list.add(pa);
		}
		return pa_list;
	}
	
	
	/**
	 * 
	* @Title: signPatientXyj 
	* @Description: 保存签约信息 
	* @param @param pa
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean savePatientInfo(Patient pa) {
		try {
			getHibernateTemplate().saveOrUpdate(pa);
			getHibernateTemplate().flush();
			log.info("保存签约信息成功!");
			return true;
		} catch (DataAccessException e) {
			log.error("保存签约信息失败!");
			return false;
		}
	}
	
	
	/**
	 * 
	* @Title: signPatientRule 
	* @Description: 保存患者设置
	* @param @param pr
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean savePatientSetting(PatientSetting ps) {
		try {
			getHibernateTemplate().saveOrUpdate(ps);
			getHibernateTemplate().flush();
			log.info("保存患者设置成功!");
			return true;
		} catch (DataAccessException e) {
			log.error("保存患者设置失败!");
			return false;
		}
	}


	/**
	 * 
	* @Title: getPatientSettingInfoByPhone 
	* @Description: 获取患者设置信息 
	* @param @param fphone
	* @param @return 
	* @return PatientSetting    返回类型 
	* @throws
	 */
	public PatientSetting getPatientSettingInfoByPhone(String fphone) {

		String sql = "";
		sql = " select fphone, fssy_low, fssy_high, fszy_low, fszy_high, fxl_low, fxl_high, fmyc_low, fmyc_high, "
			+ " fcqxt_low, fcqxt_high, fchxt1_low, fchxt1_high, fchxt2_low, fchxt2_high "
			+ " from tds_patient_setting " 
			+ " where fphone='" + fphone + "' "; 
		
		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		
		PatientSetting ps = new PatientSetting();
		if(list.size() <= 0) {
			return null;
		}
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			for (int j = 0; j < obj.length; j++) {
				ps.setFphone("" + obj[0]);
				ps.setFssy_low("" + obj[1]);
				ps.setFssy_high("" + obj[2]);
				ps.setFszy_low("" + obj[3]);
				ps.setFszy_high("" + obj[4]);
				ps.setFxl_low("" + obj[5]);
				ps.setFxl_high("" + obj[6]);
				ps.setFmyc_low("" + obj[7]);
				ps.setFmyc_high("" + obj[8]);
				ps.setFcqxt_low("" + obj[9]);
				ps.setFcqxt_high("" + obj[10]);
				ps.setFchxt1_low("" + obj[11]);
				ps.setFchxt1_high("" + obj[12]);
				ps.setFchxt2_low("" + obj[13]);
				ps.setFchxt2_high("" + obj[14]);
			}
		}
		return ps;
	}
	
	
	/**
	 * 
	* @Title: queryContact 
	* @Description: 根据身份证号获取联系人
	* @param @param fidcd
	* @param @return 
	* @return List<Contact>    返回类型 
	* @throws
	 */
	public List<Contact> queryContact(String fidcd) {
		String sql = "";
		sql = " select fidcd, fname, ftype, fphone, fid "
				+ " from tds_contact " 
				+ " where fidcd='" + fidcd + "'";

		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		if(list.size() <= 0) {
			return null;
		}
		
		this.getSession().close();
		List<Contact> ca_list = new ArrayList<Contact>();
		
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			Contact ca = new Contact();
			for (int j = 0; j < obj.length; j++) {
				ca.setFidcd("" + obj[0]);
				ca.setFname("" + obj[1]);
				ca.setFtype("" + obj[2]);
				ca.setFphone("" + obj[3]);
				ca.setFid("" + obj[4]);
			}
			ca_list.add(ca);
		}
		return ca_list;
	}
	
	/**
	 * 
	* @Title: saveContact 
	* @Description: 保存签约患者联系人
	* @param @param ca
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean saveContact(Contact ca) {
		try {
			getHibernateTemplate().saveOrUpdate(ca);
			getHibernateTemplate().flush();
			log.info("保存签约信息成功!");
			return true;
		} catch (DataAccessException e) {
			log.error("保存签约信息失败!");
			return false;
		}
	}
	
	
	/**
	 * 
	* @Title: deleteContact 
	* @Description: 删除签约患者联系人 
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean deleteContact(String fidcd, String fphone) {
		try {
			String sql = "delete tds_contact " 
					   + " where fidcd = '" + fidcd + "' "
					   + " and fphone = '" + fphone + "' ";
			
			Query query = this.getSession().createSQLQuery(sql);
			int num = query.executeUpdate();
			if (num > 0) {
				return true;
			} else {
				return true;
			}
		} catch (RuntimeException re) {
			log.error("删除签约患者联系人 失败！", re);
			throw re;
		}
	}
	
	/**
	 * 保存群组
	* @Title: saveGroup 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param group
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean saveGroup(Group group) {
		try {
			getHibernateTemplate().saveOrUpdate(group);
			getHibernateTemplate().flush();
			log.info("保存群组成功!");
			return true;
		} catch (DataAccessException e) {
			log.error("保存群组失败!");
			return false;
		}
	}
	
	/**
	 * 删除群组
	* @Title: deleteGroupByFid 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param fid
	* @param @return 
	* @return boolean    返回类型 
	* @throws
	 */
	public boolean deleteGroupByFid(String fid) {
		try {
			String sql = "delete tds_group " 
					   + " where fid = '" + fid + "' ";
			
			Query query = this.getSession().createSQLQuery(sql);
			int num = query.executeUpdate();
			if (num > 0) {
				return true;
			} else {
				return true;
			}
		} catch (RuntimeException re) {
			log.error("删除群组失败！", re);
			throw re;
		}
	}
	
	/**
	 * 获取群组列表
	* @Title: queryGroup 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param fuser
	* @param @return 
	* @return List<Group>    返回类型 
	* @throws
	 */
	public List<Group> queryGroupList(String fuser) {
		String sql = "";
		sql = " select fid, fname, fuser, ftype, fstate, fdesc "
				+ " from tds_group " 
				+ " where fuser='" + fuser + "'";

		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		if(list.size() <= 0) {
			return null;
		}
		
		//this.getSession().close();
		List<Group> g_list = new ArrayList<Group>();
		
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			Group group = new Group();
			for (int j = 0; j < obj.length; j++) {
				group.setFid("" + obj[0]);
				group.setFname("" + obj[1]);
				group.setFuser("" + obj[2]);
				group.setFtype("" + obj[3]);
				group.setFstate("" + obj[4]);
				group.setFdesc("" + obj[5]);
			}
			g_list.add(group);
		}
		return g_list;
	}
	
}
